package dao;
import model.Book;
import util.StringUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * 图书Dao类
 */
public class BookDao {
    /**
     * 图书添加
     * @param con
     * @param book
     * @return
     * @throws Exception
     */
    public int add(Connection con,Book book)throws Exception{
        String sql="insert into t_book (bookName,author,sex,price,bookTypeId,bookDesc) values(?,?,?,?,?,?)";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setString(1,book.getBookName());
        pstmt.setString(2,book.getAuthor());
        pstmt.setString(3,book.getSex());
        pstmt.setFloat(4,book.getPrice());
        pstmt.setInt(5,book.getBookTypeId());
        pstmt.setString(6,book.getBookDesc());
        return pstmt.executeUpdate();
    }
    public ResultSet list(Connection con,Book book)throws Exception{
        StringBuffer sb=new StringBuffer("select* from t_book b,t_bookType bt where b.bookTypeId=bt.id");
        if(StringUtil.isNotEmpty(book.getBookName())){
            sb.append(" and b.bookName like '%"+book.getBookName()+"%'");
        }
        if(StringUtil.isNotEmpty(book.getAuthor())){
            sb.append(" and b.author like '%"+book.getAuthor()+"%'");
        }
        if(book.getBookTypeId()!=null&&book.getBookTypeId()!=-1){
            sb.append(" and b.bookTypeId="+book.getBookTypeId());
        }
        PreparedStatement pstmt= con.prepareStatement(sb.toString());
        return pstmt.executeQuery();
    }
    public ResultSet listHouse(Connection con,Book book) throws Exception{
        StringBuffer sb=new StringBuffer("select * from t_book b,t_house bh where b.bookHouseId=bh.id");
        if(StringUtil.isNotEmpty(book.getBookName())){
            sb.append(" and b.bookName like '%"+book.getBookName()+"%'");
        }
        if(StringUtil.isNotEmpty(book.getAuthor())){
            sb.append(" and b.author like '%"+book.getAuthor()+"%'");
        }
        if(book.getBookTypeId()!=null&&book.getBookTypeId()!=-1){
            sb.append(" and b.bookTypeId="+book.getBookTypeId());
        }
        PreparedStatement pstmt= con.prepareStatement(sb.toString());
        return pstmt.executeQuery();
    }
    /**
     * 图书信息删除
     * @param con
     * @param id
     * @return
     * @throws Exception
     */
    public int delete(Connection con,String id)throws Exception{
        String sql="delete from t_book where id=?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setString(1,id);
        return pstmt.executeUpdate();
    }
    public int update(Connection con,Book book)throws Exception{
        String sql="update t_book set bookName=?,author=?,sex=?,price=?,bookDesc=?,bookTypeId=? where id=?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setString(1,book.getBookName());
        pstmt.setString(2,book.getAuthor());
        pstmt.setString(3,book.getSex());
        pstmt.setFloat(4,book.getPrice());
        pstmt.setString(5,book.getBookDesc());
        pstmt.setInt(6,book.getBookTypeId());
        pstmt.setInt(7,book.getId());
        return pstmt.executeUpdate();
    }
    /**
     * 指定图书类别下是否存在图书
     * @param con
     * @param bookTypeId
     * @return
     * @throws Exception
     */
    public boolean existBookByTypeId(Connection con,String bookTypeId)throws Exception{
        String sql="select * from t_book where bookTypeId=?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setString(1,bookTypeId);
        ResultSet rs= pstmt.executeQuery();
        return rs.next();
    }
    public boolean existHouse(Connection con,String bookHouseId) throws Exception{
        String sql="select* from t_book where bookHouseId=?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setString(1,bookHouseId);
        ResultSet rs=pstmt.executeQuery();
        return rs.next();
    }
}
